package cn.ztydata.teachingsystem.heaven.dao;

import cn.ztydata.teachingsystem.common.Page;
import cn.ztydata.teachingsystem.heaven.common.WebContext;
import cn.ztydata.teachingsystem.heaven.cons.GroupType;
import cn.ztydata.teachingsystem.heaven.cons.ResourceStatus;
import cn.ztydata.teachingsystem.heaven.entity.Attachment;
import cn.ztydata.teachingsystem.heaven.entity.Department;
import cn.ztydata.teachingsystem.heaven.entity.Resource;
import com.google.common.collect.Lists;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.*;

/**
 * 资源dao类
 * Created by chenxin on 2014/12/1.
 */
@Repository
public class ResourceDao extends BaseDao {
    @Autowired
    private WebContext webContext;

    /**
     * 验证指定专业下是否有资源
     * @param specialtyId 专业编号
     * @return Boolean
     *
     * @author fanruiping
     * @since 2014-12-12
     */
    public Boolean checkBySpecialtyId(long specialtyId){
        String sql = "SELECT COUNT(id) FROM t_resource WHERE iSpecialtyId=:specialtyId LIMIT 1";

        MapSqlParameterSource params = new MapSqlParameterSource().addValue("specialtyId", specialtyId);

        log.debug("check resource count by specialtyId");

        return namedParameterJdbcTemplate.queryForObject(sql, params, Integer.class) >= 1;
    }

    /**
     * 获取系部资源的统计数量
     * @return List
     *
     * @author cx
     * @since 2014-12-01
     */
    public List<Map<String, Object>> getTotalForDept(final Set<Long> deptIdSet) {
        StringBuilder sql = new StringBuilder("SELECT iDeptId AS deptId,COUNT(id) AS total FROM t_resource");
        MapSqlParameterSource sps = new MapSqlParameterSource();

        if (deptIdSet != null && !deptIdSet.isEmpty()) {
            sql.append(" WHERE tiStatus!=3 AND iDeptId IN (:deptIdSet)");
            sps.addValue("deptIdSet", deptIdSet);
        }

        sql.append(" GROUP BY iDeptId");

        log.debug("获取系部资源的统计数量");
        return namedParameterJdbcTemplate.queryForList(sql.toString(), sps);
    }

    /**
     * 获取系部资源总数前n名的统计
     *
     * @author chenxin
     * @since 2015/1/12
     */
    public List<Department> getTopTotalForDept(final int limit) {
        String sql = "SELECT iDeptId,COUNT(id) AS total FROM t_resource WHERE tiStatus!=3 GROUP BY iDeptId ORDER BY total DESC LIMIT :limit";

        log.debug("获取系部资源总数前n名的统计");
        return namedParameterJdbcTemplate.query(sql, new MapSqlParameterSource().addValue("limit", limit), new RowMapper<Department>() {
            @Override
            public Department mapRow(ResultSet rs, int rowNum) throws SQLException {
                Department entity = new Department();

                entity.setId(rs.getLong("iDeptId"));
                entity.setRscTotal(rs.getLong("total"));

                return entity;
            }
        });
    }

    /**
     * 获取专业资源的统计数量
     * @param deptId 系部ID
     * @return List
     *
     * @author cx
     * @since 2014-12-02
     */
    public List<Map<String, Object>> getTotalForSpec(final long deptId) {
        String sql = "SELECT iSpecialtyId AS specId,COUNT(id) AS total FROM t_resource WHERE tiStatus!=3 AND iDeptId=:deptId " +
                "GROUP BY iSpecialtyId";

        log.debug("获取专业资源的统计数量");
        return namedParameterJdbcTemplate.queryForList(sql, new MapSqlParameterSource().addValue("deptId", deptId));
    }

    /**
     * 获取专业资源的统计数量
     * @return List
     *
     * @author fanruiping
     * @since 2014-12-17
     */
    public List<Map<String, Object>> getTotalForSpec(String keyword,Set<Long> specialtyId,Integer status,Set<Long> courseId,Set<Long> uploaderId) {
        String sql = "SELECT iSpecialtyId,sSpecialtyName,COUNT(id) total FROM t_resource";

        String where = " WHERE 1=1";

        MapSqlParameterSource params = new MapSqlParameterSource();

        //判断是否限制资源名称
        if(!keyword.isEmpty()){
            where += " AND UPPER(sName) LIKE ':name'";

            params.addValue("name", keyword.toUpperCase());
        }

        //判断是否有专业编号集合
        if(!specialtyId.isEmpty()){
            where += " AND iSpecialtyId IN(:specialtyId)";

            params.addValue("specialtyId",specialtyId);
        }

        //判断是否有状态集合
        if(null != status){
            where += " AND tiStatus IN(:status)";

            if(0 == status || 2 == status){
                where += " OR (iGroupId!=0 AND tiRecommended=1)";
            }

            params.addValue("status",status);
        }

        //判断是否有课程编号集合
        if(!courseId.isEmpty()){
            if(courseId.contains(0)){
                where += " AND (iCourseId=0 OR iCourseId IS NULL)";
            }else{
                where += " AND iCourseId IN(:courseId)";
            }

            params.addValue("courseId",courseId);
        }

        //判断是否有上传者编号集合
        if(!uploaderId.isEmpty()){
            where += " AND iUploaderId IN(:uploaderId)";

            params.addValue("uploaderId",uploaderId);
        }

        sql = sql + where + " GROUP BY iSpecialtyId ORDER BY total DESC";

        log.debug("返回专业资源统计信息列表");

        return namedParameterJdbcTemplate.queryForList(sql, params);
    }

    /**
     * 获取媒体类型的专业资源统计数量
     * @return List
     *
     * @author cx
     * @since 2014-12-19
     */
    public List<Map<String, Object>> getTotalForMedia(final long specId, final long courseId) {
        StringBuilder sql = new StringBuilder("SELECT sMedia AS mediaType, COUNT(id) AS total FROM t_resource");

        MapSqlParameterSource sps = new MapSqlParameterSource();

        //where条件
        StringBuilder where = new StringBuilder(" WHERE tiGroupType=0 AND iGroupId=0 AND tiStatus!=3 AND sMedia IS NOT NULL AND sMedia != ''");

        if (specId > 0) {
            log.debug("如果：specId > 0");
            where.append(" AND iSpecialtyId=:specId");
            sps.addValue("specId", specId);
        }

        if (courseId > 0) {
            log.debug("如果：courseId > 0");
            where.append(" AND iCourseId=:courseId");
            sps.addValue("courseId", courseId);
        }else{
            log.debug("如果不限制courseId");
            where.append(" AND tiStatus=1");
        }

        //追加group by
        sql.append(where).append(" GROUP BY sMedia");

        log.debug("获取媒体类型的统计数量");
        return namedParameterJdbcTemplate.queryForList(sql.toString(), sps);
    }

    /**
     * 获取指定用户添加媒体类型的专业资源统计数量
     * @param userId 用户编号
     * @param groupType 群组类型
     * @return List
     *
     * @author fanruiping
     * @since 2015-05-06
     */
    public List<Map<String, Object>> getTotalForMediaByGroup(final  int role,final Long userId, final int groupType) {
        String where = " WHERE sMedia IS NOT NULL AND sMedia != '' AND iUploaderId=:userId AND tiRole=:role";

        MapSqlParameterSource param = new MapSqlParameterSource();
        param.addValue("userId",userId);
        param.addValue("role", role);

        //群组类型是非群组（即专业资源）
        if(0 == groupType){
            log.debug("群组类型为非群组:0 == groupType");

            where += 1 == role ? " AND tiStatus=1" : " AND (tiGroupType=0 OR (tiGroupType!=0 AND tiStatus=1))";
        }else {
            log.debug("限制资源所属资源类型:0 != groupType");

            where += " AND tiGroupType=:groupType";

            param.addValue("groupType", groupType);
        }

        String sql = "SELECT sMedia AS mediaType, COUNT(id) AS total FROM t_resource"+where+" GROUP BY sMedia";

        return namedParameterJdbcTemplate.queryForList(sql, param);
    }

    /**
     * 统计个人资源总数
     *
     * @author chenxin
     * @since 2015/1/10
     */
    public List<Map<String, Object>> getTotalForMine(final long uploaderId, final boolean courseTag) {
        StringBuilder sql = new StringBuilder("SELECT sMedia AS mediaType, COUNT(id) AS total FROM t_resource");
        MapSqlParameterSource sps = new MapSqlParameterSource();

        //where条件
        StringBuilder where = new StringBuilder(" WHERE iUploaderId=:uploaderId AND sMedia IS NOT NULL AND sMedia != ''");
        sps.addValue("uploaderId", uploaderId);

        if (courseTag) {
            log.debug("统计课程资源标识:courseTag");

            where.append(" AND iCourseId IS NOT NULL");
        }

        //追加group by
        sql.append(where).append(" GROUP BY sMedia");

        log.debug("统计个人资源总数");
        return namedParameterJdbcTemplate.queryForList(sql.toString(), sps);
    }

    /**
     * 获取资源类型的专业资源统计数量
     * @return List
     *
     * @author fanruiping
     * @since 2014-12-17
     */
    public List<Map<String, Object>> getTotalForType(String keyword, List<Long> specialtyIds, int status, Long creatorId) {
        String where = "WHERE 1=1";

        MapSqlParameterSource parameterSource = new MapSqlParameterSource();

        //判断是否限制专业
        if(!specialtyIds.isEmpty()){
            log.debug("现在资源编号集合不为空:!specialtyIds.isEmpty()");

            where += " AND iSpecialtyId IN(:specialtyId)";

            parameterSource.addValue("specialtyId", specialtyIds);
        }

        //判断是否限制名称
        if(!keyword.isEmpty()){
            log.debug("现在资源名称不为空:!keyword.isEmpty()");

            where += " AND UPPER(sName) like ':name'";

            parameterSource.addValue("name","%"+keyword.toUpperCase()+"%");
        }

        //判断是否限制状态
        if(0 == creatorId){
            log.debug("不限制资源创建人:0 == creatorId");

            where += " AND tiStatus=:status";

            if(0 == status || 2 == status){
                where += " OR (iGroupId!=0 AND tiRecommended=1)";
            }

            parameterSource.addValue("status", status);
        }else if(0 != creatorId){//判断是否限制创建者编号
            log.debug("限制资源创建人:0 != creatorId");

            where += " AND iUploaderId=:uploaderId";

            parameterSource.addValue("uploaderId", creatorId);
        }

        String sql = "SELECT iTypeId,COUNT(id) AS total FROM t_resource "+where+" GROUP BY iTypeId";

        log.debug("返回资源类型的统计数据");

        return namedParameterJdbcTemplate.queryForList(sql, parameterSource);
    }

    /**
     * 获取课程资源的统计数量
     * @param specId 专业ID
     * @return List
     *
     * @author cx
     * @since 2014-12-02
     */
    public List<Map<String, Object>> getTotalForCourse(final long specId) {
        String sql = "SELECT iCourseId AS courseId,COUNT(id) AS total FROM t_resource WHERE iSpecialtyId=:specId AND " +
                "iCourseId IS NOT NULL AND tiGroupType=0 AND iGroupId=0 GROUP BY iCourseId";

        log.debug("获取课程的资源统计数量");
        return namedParameterJdbcTemplate.queryForList(sql, new MapSqlParameterSource().addValue("specId", specId));
    }

    /**
     * 获取课程的专业资源总数
     * @param courseIdSet 课程编号集合
     * @return List
     *
     * @author chenxin
     * @since 2015/1/11
     */
    public List<Map<String, Object>> getTotalForCourses(final Set<Long> courseIdSet) {
        if (courseIdSet == null || courseIdSet.isEmpty()) {
            log.debug("如果课程ID不为空：courseIdSet == null || courseIdSet.isEmpty()");
            return Lists.newArrayList();
        }

        String sql = "SELECT iCourseId AS courseId,COUNT(id) AS total FROM t_resource WHERE iCourseId IN (:courseIdSet) " +
                "AND tiGroupType=0 AND iGroupId=0 GROUP BY iCourseId";

        log.debug("获取课程的资源总数");
        return namedParameterJdbcTemplate.queryForList(sql, new MapSqlParameterSource().addValue("courseIdSet", courseIdSet));
    }

    /**
     * 获取教师上传资源的统计数量
     * @param teacherIdList 教师ID集合
     * @return List
     *
     * @author cx
     * @since 2014-12-03
     */
    public List<Map<String, Object>> getTotalForTeachers(final List<Long> teacherIdList) {
        if (teacherIdList == null || teacherIdList.isEmpty()) {
            log.debug("教师ID集合为空:teacherIdList == null || teacherIdList.isEmpty()");
            return new ArrayList<>();
        }

        String sql = "SELECT iUploaderId AS teacherId,COUNT(id) AS total FROM t_resource WHERE iUploaderId IN (:idList) " +
                "GROUP BY iUploaderId";

        log.debug("获取教师上传资源的统计数量");
        return namedParameterJdbcTemplate.queryForList(sql, new MapSqlParameterSource().addValue("idList", teacherIdList));
    }

    /**
     * 专业资源列表
     * @param keyword 关键字
     * @param status 状态
     * @param specialtyIds 专业编号集合
     * @param resourceType 资源类型
     * @param mediaTypes 媒体类型集合
     * @param pageNum 页码
     * @param pageSize 每页显示条数
     * @return Page
     *
     * @author fanruiping
     * @since 2014-12-05
     */
    public Page<Resource> getList(Long currentUserId,String keyword, int status, Set<Long> specialtyIds, int resourceType, List<String> mediaTypes,int pageNum,int pageSize){

        MapSqlParameterSource params = new MapSqlParameterSource();

        //条件语句
        String where = "WHERE 1=1";

        //我的资源
        if(0 != currentUserId){
            log.debug("当前用户编号不为0:0 != currentUserId");

            where += " AND iUploaderId=:currentUserId AND tiRole=0";

            params.addValue("currentUserId", currentUserId);

            //无效状态
            if(5 == status){
                log.debug("数据状态为5:5 == status");

                where += " AND tiStatus=3";
            }else if(4 == status){
                log.debug("数据状态为4:4 == status");

                where += " AND tiStatus=0 AND tiKind=0 AND (sDownloadAddr='' OR sDownloadAddr IS NULL) || (iGroupId!=0 AND tiRecommended=1)";
            }else if(0 < status){
                log.debug("数据状态不为4、5");

                where += " AND tiStatus=:status";

                params.addValue("status", status-1);
            }
        }else{
            log.debug("未传入当前用户编号");

            where += 1 == status ? " AND tiStatus=1" : " AND tiStatus=:status AND (tiGroupType=0 OR (tiGroupType!=0 AND tiRecommended=1))";

            params.addValue("status", status);
        }

        //是否指定搜索
        if(!keyword.isEmpty()){
            log.debug("搜索关键字不为空:!keyword.isEmpty");

            where += " AND UPPER(sName) LIKE :keyword";
        }

        //是否指定专业
        if(!specialtyIds.isEmpty()){
            log.debug("专业编号集合不为空:!specialtyIds.isEmpty()");

            where += " AND iSpecialtyId IN (:specialtyIds)";
        }

        //是否指定资源类型
        if(0 != resourceType){
            log.debug("resourceType不为0:0 != resourceType");

            where += " AND iTypeId=:resourceType";
        }

        //是否指定媒体类型
        if(!mediaTypes.isEmpty()){
            log.debug("媒体类型集合不为空:!mediaTypes.isEmpty()");

            where += " AND sMedia IN(:mediaTypes)";
        }

        String totalSql = "SELECT COUNT(id) FROM t_resource "+where;

        String dataSql = "SELECT id,sName,sAuthor,iTypeId,sTypeName,sMedia,tiKind,iSpecialtyId,sSpecialtyName,iSize,iUploaderId,sUploaderName,dtUploadTime,tiStatus,sDownloadAddr " +
                "FROM t_resource "+where+" ORDER BY dtUploadTime DESC LIMIT :count,:pageSize";

        params.addValue("specialtyIds", specialtyIds)
                .addValue("resourceType", resourceType).addValue("mediaTypes", mediaTypes).addValue("count", (pageNum - 1) * pageSize)
                .addValue("pageSize", pageSize).addValue("keyword","%"+keyword.toUpperCase() + "%").addValue("uploaderId",currentUserId);

        int total = namedParameterJdbcTemplate.queryForObject(totalSql, params, Integer.class);

        if(total < 1){
            log.debug("未获取到资源数据:return empty page");

            return new Page<>();
        }

        List<Resource> entities = namedParameterJdbcTemplate.query(dataSql, params, CreateSimpleMapper());

        log.debug("return resource list");

        return new Page<>(total, pageSize, pageNum, entities);
    }

    /**
     * 获取最新专业资源
     * @param count 条数
     * @return List
     *
     * @author fanruiping
     * @since 2014-12-05
     */
    public List<Resource> getNewestList(int count){
        String sql = "SELECT id,sName,sAuthor,sMedia,tiKind,iTypeId,sTypeName,iSpecialtyId,sSpecialtyName,iSize,iUploaderId,sUploaderName,dtUploadTime,sDownloadAddr " +
                "FROM t_resource WHERE tiGroupType=0 AND iGroupId=0 ORDER BY dtUploadTime DESC LIMIT 0,:count";

        log.debug("return newest resource list by count");

        return namedParameterJdbcTemplate.query(sql, new MapSqlParameterSource().addValue("count", count), CreateSimpleMapper());
    }

    /**
     * 新增资源
     * @param entity
     * @return boolean
     *
     * @author fanruiping
     * @since 2014-12-09
     */
    public boolean add(Resource entity){
        String sql = "INSERT INTO t_resource(sName,sAuthor,iTypeId,sTypeName,sMedia,tiKind,iUploaderId,sUploaderName,dtUploadTime," +
                    "iDeptId,iSpecialtyId,sSpecialtyName,iCourseId,iAuditorId,dtAuditTime,tiStatus) VALUES(:name,:author,:typeId,:typeName,:mediaType,:kind,:uploaderId," +
                    ":uploaderName,:uploadTime,:deptId,:specialtyId,:specialtyName,:courseId,:auditorId,:auditTime,:status)";

        MapSqlParameterSource params = new MapSqlParameterSource().addValue("name", entity.getName()).addValue("author", entity.getAuthor())
                .addValue("typeId", entity.getTypeId()).addValue("typeName", entity.getTypeName()).addValue("kind", entity.getKind())
                .addValue("uploaderId", entity.getUploaderId()).addValue("uploaderName",entity.getUploaderName())
                .addValue("uploadTime", entity.getUploadTime()).addValue("deptId", entity.getDeptId()).addValue("specialtyId", entity.getSpecialtyId())
                .addValue("courseId", entity.getCourseId()).addValue("status", entity.getStatus()).addValue("media", entity.getMediaType())
                .addValue("uploadTime", entity.getUploadTime()).addValue("deptId", entity.getDeptId()).addValue("specialtyId", entity.getSpecialtyId())
                .addValue("specialtyName", entity.getSpecialtyName()).addValue("courseId", entity.getCourseId()).addValue("status",entity.getStatus())
                .addValue("auditorId",entity.getAuditorId()).addValue("auditTime",entity.getAuditTime()).addValue("mediaType", entity.getMediaType());

        KeyHolder keyHolder = new GeneratedKeyHolder();

        int result = namedParameterJdbcTemplate.update(sql, params, keyHolder);
        if (result == 1) {
            log.debug("资源新增成功:return true");

            entity.setId(keyHolder.getKey().longValue());
            return true;
        }

        log.debug("资源新增失败:return false");

        return false;
    }

    /**
     * 获取明细
     * @param id
     * @return Resource
     *
     * @author fanruiping
     * @since 2014-12-09
     */
    public Resource getById(long id){
        try {
            String sql = "SELECT id,sName,sAuthor,tiKind,sMedia,iSpecialtyId,sSpecialtyName,iSize,iUploaderId,sUploaderName," +
                    "dtUploadTime,iTypeId,sTypeName,sDownloadAddr,tiConvertStatus,tiStatus,iGroupId,tiGroupType FROM t_resource WHERE id=:id";

            log.debug("return resource detail by id");

            return namedParameterJdbcTemplate.queryForObject(sql, new MapSqlParameterSource().addValue("id", id), CreateMapper());
        }catch (EmptyResultDataAccessException ex){
            log.debug("catch EmptyResultDataAccessException:return null");

            return null;
        }
    }


    /**
     * 获取附件明细
     * @param id
     * @return Attachment
     *
     * @author fanruiping
     * @since 2014-12-24
     */
    public Attachment getAttachmentById(long id){
        try {
            String sql = "SELECT id,sMedia,iSize,dtUploadTime,sFileName,sDownloadAddr FROM t_resource WHERE id=:id";

            log.debug("return attachment detail by id");

            return namedParameterJdbcTemplate.queryForObject(sql, new MapSqlParameterSource().addValue("id", id), CreateAttachmentMapper());
        }catch (EmptyResultDataAccessException ex){
            log.debug("catch EmptyResultDataAccessException:return null");

            return null;
        }
    }

    /**
     * 审核数据
     * @param id 资源编号
     * @param auditorId 审核员
     * @param status 状态
     * @return int
     *
     * @author fanruiping
     * @since 2014-12-10
     */
    public int setStatus(long id, long auditorId, Timestamp auditTime, int status){
        String sql = "UPDATE t_resource SET iAuditorId=:auditorId,dtAuditTime=:auditTime,tiStatus=:status WHERE id=:id";

        MapSqlParameterSource params = new MapSqlParameterSource().addValue("auditorId",auditorId).addValue("auditTime",auditTime)
                .addValue("status", status).addValue("id", id);

        log.debug("audit resource by id");

        return namedParameterJdbcTemplate.update(sql, params);
    }

    /**
     * 删除指定资源
     * @param id 资源编号
     * @return int
     *
     * @author fanruiping
     * @since 2014-12-12
     */
    public int delete(long id){
        String sql = "DELETE FROM t_resource WHERE id=:id";

        log.debug("delete resource by resourceId");

        return namedParameterJdbcTemplate.update(sql, new MapSqlParameterSource().addValue("id", id));
    }

    /**
     * 获取资源列表信息
     *
     * @author chenxin
     * @since 2014/12/17
     */
    public List<Resource> getList(List<Long> rscIds) {
        if (rscIds == null || rscIds.isEmpty()) {
            log.debug("资源ID集合为空:rscIds == null || rscIds.isEmpty()");

            return Lists.newArrayList();
        }

        StringBuilder sql = new StringBuilder("SELECT")
                .append(" r.id,r.sName,r.sAuthor,rt.sName AS typeName,r.sMedia,r.dtUploadTime,r.iSize,r.sDownloadAddr")
                .append(" FROM t_resource r LEFT JOIN t_resource_type rt ON r.iTypeId=rt.id")
                .append(" WHERE r.id IN (:rscIds)");

        log.debug("获取资源列表信息");
        return namedParameterJdbcTemplate.query(sql.toString(), new MapSqlParameterSource().addValue("rscIds", rscIds), new RowMapper<Resource>() {
            @Override
            public Resource mapRow(ResultSet rs, int rowNum) throws SQLException {
                Resource entity = new Resource();

                entity.setId(rs.getLong("id"));
                entity.setName(rs.getString("sName"));
                entity.setAuthor(rs.getString("sAuthor"));
                entity.setTypeName(rs.getString("typeName"));
                entity.setMediaType(rs.getString("sMedia"));
                entity.setUploadTime(rs.getTimestamp("dtUploadTime"));
                entity.setSize(rs.getInt("iSize"));
                entity.setDownloadUrl(rs.getString("sDownloadAddr"));

                log.debug("返回实体entity");
                return entity;
            }
        });
    }

    /**
     * 获取通过可用的资源
     * @param resIds 资源ID集合
     * @param pageNum 当前页码
     * @param pageSize 每页条数
     * @return Page
     *
     * @author cx
     * @since 2015/03/09
     */
    public Page<Resource> getValidResources(List<Long> resIds, final int pageNum, final int pageSize) {
        MapSqlParameterSource sps = new MapSqlParameterSource();

        String whereSql = " WHERE r.id IN (:resIds) AND r.tiStatus = 1";
        sps.addValue("resIds", resIds);

        //判断是否有资源
        if (resIds == null || resIds.isEmpty()) {
            log.debug("资源ID集合为空:resIds == null || resIds.isEmpty()");

            return new Page<>();
        }

        StringBuilder sql = new StringBuilder("SELECT")
                .append(" r.id,r.sName,r.sAuthor,rt.sName AS typeName,r.sMedia,r.dtUploadTime,r.iSize,r.sDownloadAddr")
                .append(" FROM t_resource r LEFT JOIN t_resource_type rt ON r.iTypeId=rt.id");
        StringBuilder countSql = new StringBuilder("SELECT COUNT(*) FROM t_resource r");



        //统计总数
        int total = namedParameterJdbcTemplate.queryForObject(countSql.append(whereSql).toString(), sps, Integer.class);
        if (total == 0) {
            log.debug("总数为0:total == 0");

            return new Page<>();
        }

        //追加where条件和分页
        sql.append(whereSql).append(getLimitStr(pageNum, pageSize));

        List<Resource> resources = namedParameterJdbcTemplate.query(sql.toString(), sps, new RowMapper<Resource>() {
            @Override
            public Resource mapRow(ResultSet rs, int rowNum) throws SQLException {
                Resource entity = new Resource();

                entity.setId(rs.getLong("id"));
                entity.setName(rs.getString("sName"));
                entity.setAuthor(rs.getString("sAuthor"));
                entity.setTypeName(rs.getString("typeName"));
                entity.setMediaType(rs.getString("sMedia"));
                entity.setUploadTime(rs.getTimestamp("dtUploadTime"));
                entity.setSize(rs.getInt("iSize"));
                entity.setDownloadUrl(rs.getString("sDownloadAddr"));

                return entity;
            }
        });

        return new Page<>(total, pageSize, pageNum, resources);
    }

    /**
     * 获取课程资源列表
     *
     * @author chenxin
     * @since 2014/12/18
     */
    public Page<Resource> getListForCourse(final int pageNum, final int pageSize, final long courseId, final long typeId,
                                           final List<String> mediaArray, final Set<Long> rscIds, final String author, final String keyword) {
        StringBuilder sql = new StringBuilder("SELECT");
        StringBuilder countSql = new StringBuilder("SELECT COUNT(*) FROM t_resource r");

        //查询的字段
        sql.append(" r.id,r.sName,r.sAuthor,r.iTypeId,rt.sName AS typeName,r.sMedia,r.dtUploadTime,r.iSize,r.sDownloadAddr");
        //表连接
        sql.append(" FROM t_resource r LEFT JOIN t_resource_type rt ON r.iTypeId=rt.id");

        MapSqlParameterSource sps = new MapSqlParameterSource();

        //where条件
        StringBuilder where = new StringBuilder(" WHERE r.tiGroupType=0 AND r.iGroupId=0 AND r.tiStatus!=3 AND r.iCourseId=:courseId");
        sps.addValue("courseId", courseId);

        //判断资源类型
        if (typeId > 0) {
            log.debug("判断资源类型:typeId > 0");
            where.append(" AND r.iTypeId=:typeId");
            sps.addValue("typeId", typeId);
        }

        //判断媒体资源
        if (!CollectionUtils.isEmpty(mediaArray)) {
            log.debug("判断媒体资源:!CollectionUtils.isEmpty(mediaArray)");
            where.append(" AND r.sMedia IN (:mediaArray)");
            sps.addValue("mediaArray", mediaArray);
        }

        //判断资源ID
        if (!CollectionUtils.isEmpty(rscIds)) {
            log.debug("判断资源ID:!CollectionUtils.isEmpty(rscIds)");
            where.append(" AND r.id IN (:rscIds)");
            sps.addValue("rscIds", rscIds);
        }

        //判断作者
        if (!StringUtils.isEmpty(author)) {
            log.debug("判断作者:!StringUtils.isEmpty(author)");
            where.append(" AND UPPER(r.sAuthor)=:author");
            sps.addValue("author", author.toUpperCase());
        }

        /**
         * 单个关键字搜索
         */
        if (!StringUtils.isEmpty(keyword)) {
            log.debug("单个关键字搜索:!StringUtils.isEmpty(keyword)");
            where.append(" AND UPPER(r.sName) LIKE :pattern");
            sps.addValue("pattern", "%" + keyword.toUpperCase() + "%");
        }

        //获取数据总量
        int total = namedParameterJdbcTemplate.queryForObject(countSql.append(where).toString(), sps, Integer.class);
        if (total == 0) {
            log.debug("获取数据总量:total == 0");
            return new Page<>();
        }

        //追加limit限制
        sql.append(where).append(getLimitStr(pageNum, pageSize));

        //查询数据
        List<Resource> rscList = namedParameterJdbcTemplate.query(sql.toString(), sps, new RowMapper<Resource>() {
            @Override
            public Resource mapRow(ResultSet rs, int rowNum) throws SQLException {
                Resource entity = new Resource();

                entity.setId(rs.getLong("id"));
                entity.setName(rs.getString("sName"));
                entity.setAuthor(rs.getString("sAuthor"));
                entity.setTypeId(rs.getLong("iTypeId"));
                entity.setTypeName(rs.getString("typeName"));
                entity.setMediaType(rs.getString("sMedia"));
                entity.setUploadTime(rs.getTimestamp("dtUploadTime"));
                entity.setSize(rs.getInt("iSize"));
                entity.setDownloadUrl(rs.getString("sDownloadAddr"));

                return entity;
            }
        });

        log.debug("获取课程资源列表");
        return new Page<>(total, pageSize, pageNum, rscList);
    }

    /**
     * 获取资源列表（章节搜索）
     *
     * @author chenxin
     * @since 2015/1/10
     */
    public Page<Resource> getListForChapter(final int pageNum, final int pageSize, final long courseId, final long typeId,
                                           final List<String> mediaArray, final Set<Long> rscIds, final String keyword, final boolean rscTag) {
        StringBuilder sql = new StringBuilder("SELECT id,sName,iTypeId,sTypeName,sMedia FROM t_resource");
        StringBuilder countSql = new StringBuilder("SELECT COUNT(*) FROM t_resource");

        MapSqlParameterSource sps = new MapSqlParameterSource();

        //where条件
        StringBuilder where = new StringBuilder(" WHERE tiStatus!=3");
        if (rscTag) {
            log.debug("搜索课程资源标志:rscTag");

            where.append(" AND iCourseId=:courseId");
            sps.addValue("courseId", courseId);
        }
        else { //搜索审核已通过的专业资源
            where.append(" AND tiStatus=1");
        }

        //判断资源类型
        if (typeId > 0) {
            log.debug("判断资源类型:typeId > 0");

            where.append(" AND iTypeId=:typeId");
            sps.addValue("typeId", typeId);
        }

        //判断媒体资源
        if (!CollectionUtils.isEmpty(mediaArray)) {
            log.debug("判断媒体资源:!CollectionUtils.isEmpty(mediaArray)");

            where.append(" AND sMedia IN (:mediaArray)");
            sps.addValue("mediaArray", mediaArray);
        }

        //判断资源ID
        if (!CollectionUtils.isEmpty(rscIds)) {
            log.debug("判断资源ID:!CollectionUtils.isEmpty(rscIds)");

            where.append(" AND id IN (:rscIds)");
            sps.addValue("rscIds", rscIds);
        }

        /**
         * 单个关键字搜索
         */
        if (!StringUtils.isEmpty(keyword)) {
            log.debug("单个关键字搜索:!StringUtils.isEmpty(keyword)");

            where.append(" AND UPPER(sName) LIKE :pattern");
            sps.addValue("pattern", "%" + keyword.toUpperCase() + "%");
        }

        //获取数据总量
        int total = namedParameterJdbcTemplate.queryForObject(countSql.append(where).toString(), sps, Integer.class);
        if (total == 0) {
            log.debug("获取数据总量:total == 0");

            return new Page<>();
        }

        //追加limit限制
        sql.append(where).append(getLimitStr(pageNum, pageSize));

        //查询数据
        List<Resource> rscList = namedParameterJdbcTemplate.query(sql.toString(), sps, new RowMapper<Resource>() {
            @Override
            public Resource mapRow(ResultSet rs, int rowNum) throws SQLException {
                Resource entity = new Resource();

                entity.setId(rs.getLong("id"));
                entity.setName(rs.getString("sName"));
                entity.setTypeId(rs.getLong("iTypeId"));
                entity.setTypeName(rs.getString("sTypeName"));
                entity.setMediaType(rs.getString("sMedia"));

                return entity;
            }
        });

        log.debug("获取课程资源列表");
        return new Page<>(total, pageSize, pageNum, rscList);
    }

    /**
     * 更新附件信息
     * @param resource
     * @return boolean
     */
    public boolean updateAttachment(Attachment resource){
        String sql = "UPDATE t_resource SET sFileName=:fileName,sDownloadAddr=:downloadUrl,iSize=:size,sMedia=:mediaType WHERE id=:id";

        MapSqlParameterSource param = new MapSqlParameterSource().addValue("fileName",resource.getFileName()).addValue("downloadUrl", resource.getDownloadUrl())
                .addValue("size",resource.getSize()).addValue("mediaType",resource.getMediaType()).addValue("id", resource.getId());

        log.debug("update resource attachment");

        return namedParameterJdbcTemplate.update(sql,param) == 1;
    }


    /**
     * 更改资源的课程ID（用于删除课程资源）
     *
     * @author chenxin
     * @since 2014/12/24
     */
    public boolean updateCourseId(final long rscId, final long courseId) {
        String sql = "UPDATE t_resource SET iCourseId=NULL WHERE id=:rscId AND iCourseId=:courseId";
        SqlParameterSource sps = new MapSqlParameterSource().addValue("rscId", rscId)
                .addValue("courseId", courseId);

        log.debug("更改资源的课程ID（用于删除课程资源）");
        return namedParameterJdbcTemplate.update(sql, sps) == 1;
    }

    /**
     * 获取资源的课程编号
     * @param id
     * @return int
     *
     * @author fanruiping
     * @since 2014-12-24
     */
    public int getCourseIdById(Long id){
        try {
            //课程群组tiGroupType等于（1/2/3）非课程群组等于0
            String sql = "SELECT iCourseId FROM t_resource WHERE id=:id AND iCourseId IS NOT NULL AND tiGroupType = 0 LIMIT 1";

            log.debug("return courseId by resourceId");

            return namedParameterJdbcTemplate.queryForObject(sql, new MapSqlParameterSource().addValue("id", id), Integer.class);
        }catch (EmptyResultDataAccessException ex){
            log.debug("catch EmptyResultDataAccessException:return 0");

            return 0;
        }
    }

    /**
     * 查询复合条件下的可用专业资源库资源
     *
     * @param pageNum 当前页码
     * @param pageSize 每页条数
     * @param typeId 资源类型ID
     * @param resIds 资源ID集合
     * @param mediaArray 资源媒体集合
     * @param specId 专业ID
     * @return Page
     * 
     * @author cx
     * @since 2015/4/6
     */
    public Page<Resource> getValidResources(final int pageNum, final int pageSize, final long typeId, final Set<Long> resIds,
                                            final List<String> mediaArray, final long specId) {
        StringBuilder sql = new StringBuilder("SELECT id,sName,sAuthor,iTypeId,sTypeName,sMedia,dtUploadTime,iSize,sDownloadAddr FROM t_resource");
        StringBuilder countSql = new StringBuilder("SELECT COUNT(*) FROM t_resource");

        MapSqlParameterSource sps = new MapSqlParameterSource();

        //where条件，非群组资源
        StringBuilder where = new StringBuilder()
                .append(" WHERE tiStatus=").append(ResourceStatus.AUDIT_PASS);

        //资源类型
        if (typeId > 0) {
            log.debug("资源类型判定:typeId > 0");

            where.append(" AND iTypeId=:typeId");
            sps.addValue("typeId", typeId);
        }

        //资源ID
        if (!CollectionUtils.isEmpty(resIds)) {
            log.debug("资源ID集合非空:!CollectionUtils.isEmpty(resIds)");

            where.append(" AND id IN (:resIds)");
            sps.addValue("resIds", resIds);
        }

        //媒体资源
        if (!CollectionUtils.isEmpty(mediaArray)) {
            log.debug("媒体资源非空:!CollectionUtils.isEmpty(mediaArray)");

            where.append(" AND sMedia IN (:mediaArray)");
            sps.addValue("mediaArray", mediaArray);
        }

        //专业ID
        if (specId > 0) {
            log.debug("专业判定:specId > 0");

            where.append(" AND iSpecialtyId=:specId");
            sps.addValue("specId", specId);
        }

        //获取数据总量
        int total = namedParameterJdbcTemplate.queryForObject(countSql.append(where).toString(), sps, Integer.class);
        if (total == 0) {
            log.debug("获取数据总量:total == 0");
            return new Page<>();
        }

        //追加limit限制及排序
        sql.append(where).append(" ORDER BY id").append(getLimitStr(pageNum, pageSize));

        //查询数据
        List<Resource> resList = namedParameterJdbcTemplate.query(sql.toString(), sps, new RowMapper<Resource>() {
            @Override
            public Resource mapRow(ResultSet rs, int rowNum) throws SQLException {
                Resource entity = new Resource();

                entity.setId(rs.getLong("id"));
                entity.setName(rs.getString("sName"));
                entity.setAuthor(rs.getString("sAuthor"));
                entity.setTypeId(rs.getLong("iTypeId"));
                entity.setTypeName(rs.getString("sTypeName"));
                entity.setMediaType(rs.getString("sMedia"));
                entity.setUploadTime(rs.getTimestamp("dtUploadTime"));
                entity.setSize(rs.getInt("iSize"));
                entity.setDownloadUrl(rs.getString("sDownloadAddr"));

                return entity;
            }
        });

        log.debug("查询复合条件下的可用专业资源库资源");
        return new Page<>(total, pageSize, pageNum, resList);
    }

    /**
     * ResultSet转为Resource实体(列表)
     * @return RowMapper
     *
     * @author fanruiping
     * @since 2014-12-05
     */
    private RowMapper<Resource> CreateSimpleMapper(){
        return new RowMapper<Resource>() {
            @Override
            public Resource mapRow(ResultSet rs, int i) throws SQLException {
                Resource entity = new Resource();

                entity.setId(rs.getLong("id"));
                entity.setName(rs.getString("sName"));
                entity.setAuthor(rs.getString("sAuthor"));
                entity.setMediaType(rs.getString("sMedia"));
                entity.setSpecialtyId(rs.getLong("iSpecialtyId"));
                entity.setSpecialtyName(rs.getString("sSpecialtyName"));
                entity.setKind(rs.getInt("tiKind"));
                entity.setSize(rs.getInt("iSize"));
                entity.setUploaderId(rs.getLong("iUploaderId"));
                entity.setUploaderName(rs.getString("sUploaderName"));
                entity.setUploadTime(rs.getTimestamp("dtUploadTime"));
                entity.setDownloadUrl(rs.getString("sDownloadAddr"));
                entity.setTypeId(rs.getLong("iTypeId"));
                entity.setTypeName(rs.getString("sTypeName"));

                return entity;
            }
        };
    }

    /**
     * ResultSet转为Resource实体（明细）
     * @return RowMapper
     *
     * @author fanruiping
     * @since 2014-12-05
     */
    private RowMapper<Resource> CreateMapper(){
        return new RowMapper<Resource>() {
            @Override
            public Resource mapRow(ResultSet rs, int i) throws SQLException {
                Resource entity = new Resource();

                entity.setId(rs.getLong("id"));
                entity.setName(rs.getString("sName"));
                entity.setAuthor(rs.getString("sAuthor"));
                entity.setMediaType(rs.getString("sMedia"));
                entity.setKind(rs.getInt("tiKind"));
                entity.setSpecialtyId(rs.getLong("iSpecialtyId"));
                entity.setSpecialtyName(rs.getString("sSpecialtyName"));
                entity.setSize(rs.getInt("iSize"));
                entity.setUploaderId(rs.getLong("iUploaderId"));
                entity.setUploaderName(rs.getString("sUploaderName"));
                entity.setUploadTime(rs.getTimestamp("dtUploadTime"));
                entity.setTypeId(rs.getLong("iTypeId"));
                entity.setTypeName(rs.getString("sTypeName"));
                entity.setDownloadUrl(rs.getString("sDownloadAddr"));
                entity.setStatus(rs.getInt("tiStatus"));
                entity.setConverterStatus(rs.getInt("tiConvertStatus"));
                entity.setGroupType(rs.getInt("tiGroupType"));
                entity.setGroupId(rs.getLong("iGroupId"));

                return entity;
            }
        };
    }


    /**
     * ResultSet转为Resource实体（明细）
     * @return RowMapper
     *
     * @author fanruiping
     * @since 2014-12-05
     */
    private RowMapper<Attachment> CreateAttachmentMapper(){
        return new RowMapper<Attachment>() {
            @Override
            public Attachment mapRow(ResultSet rs, int i) throws SQLException {
                Attachment entity = new Attachment();

                entity.setId(rs.getLong("id"));
                entity.setMediaType(rs.getString("sMedia"));
                entity.setSize(rs.getInt("iSize"));
                entity.setUploadTime(rs.getTimestamp("dtUploadTime"));
                entity.setDownloadUrl(rs.getString("sDownloadAddr"));
                entity.setFileName(rs.getString("sFileName"));

                return entity;
            }
        };
    }


    /**
     * 更新资源所属专业名称
     * @param specialtyId
     * @param specialtyName
     * @return boolean
     *
     * @author fanruiping
     * @since 2015-01-06
     */
    public int updateSpecialtyName(final long specialtyId,final String specialtyName){
        String sql = "UPDATE t_resource SET sSpecialtyName=:specialtyName WHERE iSpecialtyId=:specialtyId";

        SqlParameterSource sps = new MapSqlParameterSource().addValue("specialtyId", specialtyId)
                .addValue("specialtyName", specialtyName);

        log.debug("update resource specialtyName");

        return namedParameterJdbcTemplate.update(sql, sps);
    }

    /**
     * 更新资源上传者名称
     * @param uploaderId
     * @param uploaderName
     * @return boolean
     *
     * @author fanruiping
     * @since 2015-01-06
     */
    public int updateUploaderName(final long uploaderId,final String uploaderName){
        String sql = "UPDATE t_resource SET sUploaderName=:uploaderName WHERE iUploaderId=:uploaderId";

        SqlParameterSource sps = new MapSqlParameterSource().addValue("uploaderId", uploaderId)
                .addValue("uploaderName", uploaderName);

        log.debug("update resource uploaderName");

        return namedParameterJdbcTemplate.update(sql, sps);
    }

    /**
     * 设置资源为无效资源
     * @param id 资源编号
     * @return Boolean
     *
     * @author fanruiping
     * @since 2015-02-11
     */
    public boolean setInvalid(long id){
        String sql = "UPDATE t_resource SET tiStatus=3 WHERE id=:id";

        SqlParameterSource params = new MapSqlParameterSource().addValue("id",id);

        log.debug("set resource status is invalid");

        return  namedParameterJdbcTemplate.update(sql,params) >= 0;
    }


    /**
     * 推荐资源
     *
     * @param mapCommend
     * @return int
     * @author lyj
     * @since 2015-3-31
     */
    public int recommend(final Map<String, Object> mapCommend) {
        StringBuilder sql = new StringBuilder("UPDATE t_resource SET tiRecommended = :recommend WHERE id = :id");

        MapSqlParameterSource param = new MapSqlParameterSource()
                .addValue("id", mapCommend.get("id"))
                .addValue("recommend", mapCommend.get("pushed"));

        log.debug("推荐资源");
        return namedParameterJdbcTemplate.update(sql.toString(), param);
    }


    /**
     * 获取热门资源信息列表
     *
     * @param rscIds 资源ID
     * @return List<Resource>
     * @author lyj
     * @since 2015-4-1
     */
    public List<Resource> getHotList(final Set<Long> rscIds, final int first) {

        if (rscIds == null || rscIds.isEmpty()) {
            log.debug("rscIds == null || rscIds.isEmpty()");
            return Lists.newArrayList();
        }

        StringBuilder sql = new StringBuilder("SELECT r.id, r.sName, rt.sName AS typeName, r.dtUploadTime, r.tiKind, r.sMedia FROM t_resource r LEFT JOIN t_resource_type rt ON r.iTypeId = rt.id WHERE r.id IN (:rscIds)");

        if (first > 0) {
            sql.append(" LIMIT 0,:first");
        }
        log.debug("获取热门资源信息列表");
        return namedParameterJdbcTemplate.query(sql.toString(), new MapSqlParameterSource().addValue("rscIds", rscIds).addValue("first", first), new RowMapper<Resource>() {
            @Override
            public Resource mapRow(ResultSet rs, int rowNum) throws SQLException {
                Resource entity = new Resource();

                entity.setId(rs.getLong("id"));
                entity.setName(rs.getString("sName"));
                entity.setTypeName(rs.getString("typeName"));
                entity.setUploadTime(rs.getTimestamp("dtUploadTime"));
                entity.setKind(rs.getInt("tiKind"));
                entity.setMediaType(rs.getString("sMedia"));

                return entity;
            }
        });
    }


    /**
     * 获取群组资源列表
     *
     * @param groupType  群组类型
     * @param groupId    群组ID
     * @param upLoaderId 上传者ID
     * @param typeId     资源类型ID
     * @param keyword    关键字
     * @param pageNum    当前页码
     * @param pageSize   每页条数
     * @param rscIds     资源ID
     * @return Page<Resource>
     * @author lyj
     * @since 2015-4-1
     */
    public Page<Resource> getGroupList(final int groupType, final long groupId, final long upLoaderId, final int role, final long typeId, final String keyword, final int pageNum, final int pageSize, final List<Long> rscIds) {

        StringBuilder sql = new StringBuilder("SELECT r.id, r.sName, r.sAuthor, rt.sName AS typeName, r.sSpecialtyName, r.dtUploadTime, r.tiKind, r.iSize, r.sDownloadAddr, r.sMedia, r.tiRecommended FROM t_resource r LEFT JOIN t_resource_type rt ON r.iTypeId = rt.id ");
        StringBuilder sqlCount = new StringBuilder("SELECT COUNT(r.id) FROM t_resource r");

        StringBuilder where = new StringBuilder(" where 1 = 1");
        MapSqlParameterSource param = new MapSqlParameterSource();

        //过滤群组类型
        if (groupType >= 1 && groupType <= 3) {
            log.debug("过滤群组类型groupType >= 1 && groupType <= 3");
            where.append(" AND r.tiGroupType = :groupType");
            param.addValue("groupType", groupType);
        }

        //过滤群组ID
        if (groupId > 0) {
            log.debug("过滤群组IDgroupId > 0");
            where.append(" AND r.iGroupId = :groupId");
            param.addValue("groupId", groupId);
        }

        //过滤上传人ID
        if (upLoaderId > 0) {
            log.debug("过滤上传人IDupLoaderId > 0");
            where.append(" AND r.iUploaderId = :upLoaderId AND r.tiRole = :role ");
            param.addValue("upLoaderId", upLoaderId);
            param.addValue("role", role);
        }

        //过滤资源类型ID
        if (typeId > 0) {
            log.debug("过滤资源类型IDtypeId > 0");
            where.append(" AND r.iTypeId = :typeId");
            param.addValue("typeId", typeId);
        }

        // 单个关键字搜索
        if (!StringUtils.isEmpty(keyword)) {
            log.debug("单个关键字搜索!StringUtils.isEmpty(keyword)");
            where.append(" AND UPPER(r.sName) LIKE :pattern");
            param.addValue("pattern", "%" + keyword.toUpperCase() + "%");
        }

        //判断资源ID
        if (!CollectionUtils.isEmpty(rscIds)) {
            log.debug("判断资源ID!CollectionUtils.isEmpty(rscIds)");
            where.append(" AND r.id IN (:rscIds)");
            param.addValue("rscIds", rscIds);
        }


        //获取数据总量
        int total = namedParameterJdbcTemplate.queryForObject(sqlCount.append(where).toString(), param, Integer.class);
        if (total == 0) {
            log.debug("获取数据总量total == 0");
            return new Page<>();
        }

        //追加排序
        where.append(" ORDER BY r.dtUploadTime DESC");

        //追加limit限制
        where.append(getLimitStr(pageNum, pageSize));

        //查询数据
        List<Resource> rscList = namedParameterJdbcTemplate.query(sql.append(where).toString(), param, new RowMapper<Resource>() {
            @Override
            public Resource mapRow(ResultSet rs, int rowNum) throws SQLException {
                Resource entity = new Resource();

                entity.setId(rs.getLong("id"));
                entity.setName(rs.getString("sName"));
                entity.setAuthor(rs.getString("sAuthor"));
                entity.setTypeName(rs.getString("typeName"));
                entity.setSpecialtyName(rs.getString("sSpecialtyName"));
                entity.setUploadTime(rs.getTimestamp("dtUploadTime"));
                entity.setKind(rs.getInt("tiKind"));
                entity.setSize(rs.getInt("iSize"));
                entity.setDownloadUrl(rs.getString("sDownloadAddr"));
                entity.setMediaType(rs.getString("sMedia"));
                entity.setRecommended(rs.getInt("tiRecommended"));
                entity.setBest(0);

                return entity;
            }
        });

        log.debug("获取群组资源列表");
        return new Page<>(total, pageSize, pageNum, rscList);
    }


    /**
     * 新增课程群组资源
     *
     * @param entity
     * @return boolean
     * @author lyj
     * @since 2015-4-3
     */
    public boolean addGroupResource(Resource entity){
        String sql = "INSERT INTO t_resource(sName, sAuthor, iTypeId, sTypeName, sMedia, tiKind, iUploaderId, sUploaderName, dtUploadTime, iDeptId, iSpecialtyId, " +
                "sSpecialtyName, iCourseId, iAuditorId, dtAuditTime,tiStatus, tiRole, tiGroupType, iGroupId, tiRecommended) VALUES(:name, :author, :typeId, :typeName, :media, :kind," +
                " :uploaderId, :uploaderName, NOW(), :deptId, :specialtyId, :specialtyName, :courseId, :auditorId,:auditTime,:status, :role, :groupType, :groupId, :recommended)";

        MapSqlParameterSource params = new MapSqlParameterSource()
                .addValue("name", entity.getName())
                .addValue("author", entity.getAuthor())
                .addValue("typeId", entity.getTypeId())
                .addValue("typeName", entity.getTypeName())
                .addValue("media", entity.getMediaType())

                .addValue("kind", entity.getKind())
                .addValue("uploaderId", entity.getUploaderId())
                .addValue("uploaderName",entity.getUploaderName())
                .addValue("deptId", entity.getDeptId())
                .addValue("specialtyId", entity.getSpecialtyId())

                .addValue("specialtyName", entity.getSpecialtyName())
                .addValue("courseId", entity.getCourseId())
                .addValue("status", entity.getStatus())
                .addValue("role", entity.getRole())
                .addValue("groupType", entity.getGroupType())
                .addValue("auditorId", entity.getAuditorId())
                .addValue("auditTime", entity.getAuditTime())

                .addValue("groupId", entity.getGroupId())
                .addValue("recommended", entity.getRecommended());

        KeyHolder keyHolder = new GeneratedKeyHolder();

        int result = namedParameterJdbcTemplate.update(sql, params, keyHolder);
        if (result == 1) {
            log.debug("result == 1");
            entity.setId(keyHolder.getKey().longValue());
            return true;
        }
        log.debug("新增课程群组资源");
        return false;
    }



	/**
     * 线下作业引用资源列表
     * @param specialtyId 专业编号
     * @param media 媒体类型
     * @param type 资源类型
     * @param pageNum 页码
     * @param pageSize 每页显示条数
     * @return Page
     *
     * @author fanruiping
     * @since 2015-03-31
     */
    public Page<Resource> recommendListForTest(Long specialtyId,Set<String> media,Set<Integer> type,int pageNum,int pageSize){
        MapSqlParameterSource param = new MapSqlParameterSource();

        String where = " WHERE iSpecialtyId=:specialtyId AND sMedia IN(:media) AND iTypeId IN(:type) AND tiStatus=1";

        param.addValue("specialtyId",specialtyId).addValue("media",media).addValue("type",type);

        String dataSql = "SELECT id,sName FROM t_resource" + where + getLimitStr(pageNum,pageSize);

        String totalSql = "SELECT COUNT(id) total FROM t_resource" + where;

        int total = namedParameterJdbcTemplate.queryForObject(totalSql,param,Integer.class);

        //判断数据条数
        if(0 == total){
            log.debug("未获取资源列表数据:0 == total");

            return new Page<>();
        }

        //获取列表数据
        List<Resource> list = namedParameterJdbcTemplate.query(dataSql, param, new RowMapper<Resource>() {
            @Override
            public Resource mapRow(ResultSet rs, int i) throws SQLException {
                Resource resource = new Resource();

                resource.setId(rs.getLong("id"));
                resource.setName(rs.getString("sName"));

                return resource;
            }
        });

        return new Page<>(total,pageSize,pageNum,list);
    }

    /**
     * 获取资源附件信息
     * @param resourceId 资源编号
     * @return Resource
     *
     * @author fanruiping
     * @since 2015-04-11
     */
    public Resource getFileDetail(Long resourceId){
        try {
            String sql = "SELECT r.sDownloadAddr,r.sFileName,ri.sShowAddr FROM t_resource r LEFT JOIN t_resource_info ri " +
                    "ON r.id=ri.iResourceId WHERE r.id=:id LIMIT 1";

            return namedParameterJdbcTemplate.queryForObject(sql, new MapSqlParameterSource().addValue("id", resourceId), new RowMapper<Resource>() {
                @Override
                public Resource mapRow(ResultSet resultSet, int i) throws SQLException {
                    Resource resource = new Resource();

                    resource.setDownloadUrl(resultSet.getString("sDownloadAddr"));
                    resource.setShowAddr(resultSet.getString("sShowAddr"));
                    resource.setOrigin(resultSet.getString("sFileName"));

                    return resource;
                }
            });
        }catch (EmptyResultDataAccessException ex){
            log.debug("未获取到指定资源附件信息:EmptyResultDataAccessException ex");

            return null;
        }
    }

    /**
     * 获取媒体类型的统计数量
     * @return List
     *
     * @author lyj
     * @since 2015-1-15
     */
    public List<Map<String, Object>> getTotalForMedia(final long groupId, final int groupType) {
        StringBuilder sql = new StringBuilder("SELECT sMedia AS mediaType, COUNT(id) AS total FROM t_resource");

        MapSqlParameterSource param = new MapSqlParameterSource();

        //where条件
        StringBuilder where = new StringBuilder(" WHERE tiStatus!=3 AND sMedia IS NOT NULL AND sMedia != ''");

        if (groupId > 0) {
            log.debug("groupId > 0");

            where.append(" AND iGroupId = :groupId");
            param.addValue("groupId", groupId);
        }

        if (groupType > 0) {
            log.debug("groupType > 0");

            where.append(" AND tiGroupType = :groupType");
            param.addValue("groupType", groupType);
        }
        //追加group by
        sql.append(where).append(" GROUP BY sMedia");

        log.debug("获取媒体类型的统计数量");
        return namedParameterJdbcTemplate.queryForList(sql.toString(), param);
    }

    /**
     * 获取资源数据
     * @param userId 创建人编号
     * @param role 创建人角色（0老师 1学生 2全部）
     * @param groupType 群组类型(0:非群组（专业资源），1：课程群组，2：专业群组，3：班级群组)
     * @param searchKey 搜索关键字
     * @param rscIds 资源编号集合
     * @return Map
     *
     * @author fanruiping
     * @since 2015-05-06
     */
    public Map<Long,Long> getTotalByGroup(Long userId,int role,final int groupType,String searchKey,List<Long> rscIds){
        //保存最终结果（id<=>total）
        final Map<Long,Long> result = new HashMap<>();

        MapSqlParameterSource params = new MapSqlParameterSource();

        String where = " WHERE 1=1";

        //是否限制创建用户编号
        if(0 != userId){
            log.debug("限制创建人编号:0 != userId");

            where += " AND iUploaderId=:userId";

            params.addValue("userId",userId);
        }

        //获取的编号字段名
        String fieldName = "iSpecialtyId";

        //判断用户角色及群组类型
        if(0 == role && 0 == groupType){
            log.debug("当前用户为教师且群组类型为专业群组:0 == role && 0 == groupType");

            where += " AND (tiGroupType=0 OR (tiGroupType!=0 AND tiStatus=1))";
        }else if(1 == role && 0 == groupType){//学生的专业资源
            log.debug("当前用户为学生且群组类型为:1 == role && 0 == groupType");

            where += " AND tiStatus=1";
        }else if(0 != groupType){//用户的群组资源
            log.debug("限制群组类型:0 != groupType");

            fieldName = "iGroupId";

            where += " AND tiGroupType=:groupType";

            params.addValue("groupType", groupType);
        }

        //限制创建人角色
        if(2 != role){
            log.debug("限制创建人角色:2 != role");

            where += " AND tiRole=:role";

            params.addValue("role", role);
        }

        //限制资源编号集合
        if(!rscIds.isEmpty()){
            log.debug("现在资源编号:!rscIds.isEmpty()");

            where += " AND id IN(:rscIds)";

            params.addValue("rscIds",rscIds);
        }

        //限制搜索关键字
        if(!searchKey.isEmpty()){
            log.debug("限制资源名称匹配关键字:!searchKey.isEmpty()");

            where += " AND UPPER(sName) LIKE :keyword";

            params.addValue("keyword","%"+searchKey.toUpperCase()+"%");
        }

        String sql = "SELECT "+fieldName+",COUNT(id) total FROM t_resource"  + where +" GROUP BY "+fieldName;

        namedParameterJdbcTemplate.query(sql, params, new RowMapper<Object>() {
            @Override
            public Object mapRow(ResultSet resultSet, int i) throws SQLException {
                if(groupType == 0) {
                    log.debug("群组类型为专业资源:groupType == 0");

                    result.put(resultSet.getLong("iSpecialtyId"), resultSet.getLong("total"));
                }else {
                    log.debug("群组类型不是专业资源:groupType != 0");

                    result.put(resultSet.getLong("iGroupId"), resultSet.getLong("total"));
                }

                return null;
            }
        });

        return result;
    }

    /**
     * 获取指定用户添加的资源列表
     * @param userId 用户编号
     * @param groupId 群组编号（0为全部）
     * @param mediaTypes 媒体类型（0为全部）
     * @param status 状态（0为全部）
     * @param type 资源类型（0为全部）
     * @param keyword 搜索关键字
     * @param groupType 群组类型（0：专业、1：课程、2：专业群组、3：班级）
     * @param page 页码
     * @param pageSize 分页
     * @return Page
     *
     * @author fanruiping
     * @since 2015-05-06
     */
    public Page<Resource> getListForMine(Long userId,int role,Long groupId,List<String> mediaTypes,int status,
                                         int type,String keyword,int groupType,List<Long> rscIds,int page,int pageSize){
        String where = " WHERE 1=1";

        MapSqlParameterSource params = new MapSqlParameterSource();

        //限制群组编号或专业编号
        if(0 != groupId){
            log.debug("限制资源所属群组编号:0 != groupId");

            //专业资源
            if(groupType == 0){
                log.debug("群组类型为非群组:groupType == 0");

                where += " AND iSpecialtyId=:groupId";

                where += 1 == role ? " AND tiStatus=1":"";
            }else {//群组资源
                log.debug("限制群组类型:groupType != 0");

                where += " AND tiGroupType=:groupType AND iGroupId=:groupId";
            }

            params.addValue("groupId", groupId);
            params.addValue("groupType", groupType);
        }else if(0 == groupType){
            where += role == 0 ? " AND (tiGroupType=0 OR (tiGroupType!=0 AND tiStatus=1))" : " AND tiStatus=1";
        }else if(0 != groupType){
            log.debug("限制群组类型且不限制群组编号:0 == groupId");

            where += " AND tiGroupType=:groupType";

            params.addValue("groupType", groupType);
        }

        //判断是否限制创建人
        if(0 != userId){
            log.debug("限制资源创建人:0 != userId");

            where += " AND iUploaderId=:userId";

            params.addValue("userId", userId);
        }

        //限制创建人角色
        if(2 != role){
            log.debug("限制资源创建人角色:2 != role");

            where += " AND tiRole=:role";

            params.addValue("role",role);
        }

        //判断是否限制媒体类型
        if(!mediaTypes.isEmpty()){
            log.debug("限制资源媒体类型:!mediaTypes.isEmpty()");

            where += " AND sMedia IN(:media)";

            params.addValue("media", mediaTypes);
        }

        //判断是否限制状态
        if(0 != status){
            log.debug("限制资源状态:0 != status");

            //状态为
            if(1 == status || 2 == status || 3 == status){
                log.debug("资源状态为未通过、通过、忽略:1 == status || 2 == status || 3 == status");

                where += " AND tiStatus=:status";

                params.addValue("status",status-1);
            }else if(5 == status){
                log.debug("资源状态为无效:5 == status");

                where += " AND tiStatus=3";
            }else{
                log.debug("资源状态为未完成状态");

                where += " AND tiStatus=0 AND tiKind=0 AND (sDownloadAddr='' || sDownloadAddr IS NULL)";
            }
        }

        //判断是否限制资源类型
        if(0 != type){
            log.debug("限制资源类型:0 != type");

            where += " AND iTypeId=:typeId";

            params.addValue("typeId",type);
        }

        //匹配搜索名称
        if(!keyword.isEmpty()){
            log.debug("限制资源名称匹配搜索关键字:!keyword.isEmpty()");

            where += " AND UPPER(sName) LIKE :keyword";

            params.addValue("keyword","%"+keyword.toUpperCase()+"%");
        }

        //限制资源编号集合
        if(!rscIds.isEmpty()){
            log.debug("限制资源编号:!rscIds.isEmpty()");

            where += " AND id IN(:rscIds)";

            params.addValue("rscIds",rscIds);
        }

        String totalSql = "SELECT COUNT(id) FROM t_resource "+where;

        int total = namedParameterJdbcTemplate.queryForObject(totalSql,params,Integer.class);

        //判断数据条数
        if(0 == total){
            log.debug("未获取到资源数据:0 == total");

            return new Page<>();
        }

        String dataSql = "SELECT id,sName,sAuthor,iTypeId,sTypeName,sMedia,tiKind,iSpecialtyId,sSpecialtyName," +
                "iSize,dtUploadTime,sDownloadAddr,iGroupId,tiGroupType FROM t_resource "+where+" ORDER BY dtUploadTime DESC"+getLimitStr(page,pageSize);

        List<Resource> resources = namedParameterJdbcTemplate.query(dataSql, params, new RowMapper<Resource>() {
            @Override
            public Resource mapRow(ResultSet rs, int i) throws SQLException {
                Resource entity = new Resource();

                entity.setId(rs.getLong("id"));
                entity.setName(rs.getString("sName"));
                entity.setAuthor(rs.getString("sAuthor"));
                entity.setMediaType(rs.getString("sMedia"));
                entity.setSpecialtyId(rs.getLong("iSpecialtyId"));
                entity.setSpecialtyName(rs.getString("sSpecialtyName"));
                entity.setKind(rs.getInt("tiKind"));
                entity.setSize(rs.getInt("iSize"));
                entity.setUploadTime(rs.getTimestamp("dtUploadTime"));
                entity.setDownloadUrl(rs.getString("sDownloadAddr"));
                entity.setTypeId(rs.getLong("iTypeId"));
                entity.setTypeName(rs.getString("sTypeName"));
                entity.setGroupId(rs.getLong("iGroupId"));
                entity.setGroupType(rs.getInt("tiGroupType"));

                return entity;
            }
        });

        return new Page<>(total,pageSize,page,resources);
    }

    /**
     * 获取指定用户创建的资源类型的专业资源统计数量
     * @param userId 用户编号
     * @param role 角色（0：教师，1：学生，2：全部）
     * @param keyword 搜索关键字
     * @param rscIds 资源编号集合
     * @param groupId 群组编号（或专业编号）
     * @param groupType 群组类型（0：专业、1：课程、2：专业群组、3：班级）
     * @return Map
     *
     * @author fanruiping
     * @since 2015-05-07
     */
    public Map<Long,Long> getTotalForTypeAndGroup(Long userId,int role,String keyword, List<Long> rscIds,Long groupId,int groupType) {
        //保存最终结果（id<=>total）
        final Map<Long,Long> result = new HashMap<>();

        String where = "WHERE 1=1";

        MapSqlParameterSource parameterSource = new MapSqlParameterSource();

        //判断群组类型为非群组
        if(groupType == 0){
            log.debug("群组类型为非群组:groupType == 0");

            where += 1 == role ? " AND tiStatus=1" : " AND (tiGroupType=0 OR (tiGroupType!=0 AND tiStatus=1))";

            where += 0 != groupId ? " AND iSpecialtyId=:groupId" : "";
        }else{
            log.debug("限制数据所属的群组类型:groupType != 0");

            where += " AND tiGroupType=:groupType";

            where += 0 != groupId ? " AND iGroupId=:groupId" : "";
        }
        parameterSource.addValue("groupType",groupType);
        parameterSource.addValue("groupId", groupId);

        //判断是否限制资源编号集合
        if(!rscIds.isEmpty()){
            log.debug("资源编号集合不为空:!rscIds.isEmpty()");

            where += " AND id IN(:rscIds)";

            parameterSource.addValue("rscIds", rscIds);
        }

        //判断是否限制名称
        if(!keyword.isEmpty()){
            log.debug("限制资源名称匹配搜索关键字:!keyword.isEmpty()");

            where += " AND UPPER(sName) like :name";

            parameterSource.addValue("name","%"+keyword.toUpperCase()+"%");
        }

        //判断是否限制创建者编号
        if(0 != userId){
            log.debug("限制创建人编号:0 != userId");

            where += " AND iUploaderId=:uploaderId";

            parameterSource.addValue("uploaderId", userId);
        }

        //限制创建人角色
        if(2 != role){
            log.debug("限制创建人角色:2 != role");

            where += " AND tiRole=:role";

            parameterSource.addValue("role",role);
        }

        String sql = "SELECT iTypeId,COUNT(id) AS total FROM t_resource "+where+" GROUP BY iTypeId";

        namedParameterJdbcTemplate.query(sql, parameterSource, new RowMapper<Object>() {
            @Override
            public Object mapRow(ResultSet resultSet, int i) throws SQLException {
                result.put(resultSet.getLong("iTypeId"), resultSet.getLong("total"));

                return null;
            }
        });

        return result;
    }
}
